CREATE PROCEDURE Address_FindAll
AS
BEGIN
    Select 
    AddressId,
    CompanyName,
    Address1,
    Address2,
    PhoneNumber,
    PhoneNumber2,
    Fax,
    AlternateEmail,
    AlternateEmail2,
    City,
    State,
    ZipCode,
    Country
    from Address
END
GO

CREATE PROCEDURE Address_FindById
@AddressId int
AS
BEGIN
    Select * from Address where AddressId = @AddressId
END
GO

CREATE PROCEDURE Address_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from Address where AddressId = @name
END
GO

CREATE PROCEDURE Address_Insert
        @AddressId int ,
        @CompanyName nvarchar (50),
        @Address1 nvarchar (50),
        @Address2 nvarchar (50),
        @PhoneNumber nvarchar (50),
        @PhoneNumber2 nvarchar (50),
        @Fax nvarchar (50),
        @AlternateEmail nvarchar (50),
        @AlternateEmail2 nvarchar (50),
        @City nvarchar (50),
        @State nvarchar (50),
        @ZipCode nvarchar (50),
        @Country nvarchar (50)
AS
BEGIN
INSERT INTO Address
(
        AddressId,
        CompanyName,
        Address1,
        Address2,
        PhoneNumber,
        PhoneNumber2,
        Fax,
        AlternateEmail,
        AlternateEmail2,
        City,
        State,
        ZipCode,
        Country
)
VALUES
(
        @AddressId,
        @CompanyName,
        @Address1,
        @Address2,
        @PhoneNumber,
        @PhoneNumber2,
        @Fax,
        @AlternateEmail,
        @AlternateEmail2,
        @City,
        @State,
        @ZipCode,
        @Country
)
select @AddressId = @@IDENTITY
END
GO

CREATE PROCEDURE Address_Update
        @AddressId int ,
        @CompanyName nvarchar (50),  
        @Address1 nvarchar (50),  
        @Address2 nvarchar (50),  
        @PhoneNumber nvarchar (50),  
        @PhoneNumber2 nvarchar (50),  
        @Fax nvarchar (50),  
        @AlternateEmail nvarchar (50),  
        @AlternateEmail2 nvarchar (50),  
        @City nvarchar (50),  
        @State nvarchar (50),  
        @ZipCode nvarchar (50),  
        @Country nvarchar (50),
AS
BEGIN
UPDATE  Address SET
    CompanyName = @CompanyName,
    Address1 = @Address1,
    Address2 = @Address2,
    PhoneNumber = @PhoneNumber,
    PhoneNumber2 = @PhoneNumber2,
    Fax = @Fax,
    AlternateEmail = @AlternateEmail,
    AlternateEmail2 = @AlternateEmail2,
    City = @City,
    State = @State,
    ZipCode = @ZipCode,
    Country = @Country
    WHERE AddressId = @AddressId
END
GO

CREATE PROCEDURE Address_Delete
    @AddressId int
AS
BEGIN
    DELETE FROM Address where AddressId = @AddressId
END
GO

CREATE PROCEDURE Address_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(AddressId) FROM Address;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  Address
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc Address_SetActive
@AddressId int
AS
declare @Type bit
SELECT @Type =  IsActive from Address where AddressId = @AddressId
BEGIN
IF(@Type = 1)
Update Address set IsActive = 0 where AddressId = @AddressId
else
Update Address set IsActive = 1 where AddressId = @AddressId
END
GO

CREATE proc Address_SetSortIndex
@AddressId int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  Address where AddressId = @AddressId
BEGIN
IF(@Type = 1)
Update Address set SortIndex =  @CurrentSort + 1 where AddressId = @AddressId
else
Update Address set SortIndex =  @CurrentSort - 1 where AddressId = @AddressId
END
GO

CREATE PROCEDURE Address_RemoveSelectedItem
    @AddressId int
AS
BEGIN
    UPDATE Address SET IsDeleted = 1 WHERE AddressId = @AddressId
END
GO

CREATE PROCEDURE Address_RestoreSelectedItem
    @AddressId int
AS
BEGIN
    UPDATE Address SET IsDeleted = 0 WHERE AddressId = @AddressId
END
GO

CREATE PROCEDURE Address_RemoveAll
    @AddressId int
AS
BEGIN
    UPDATE Address SET IsDeleted = 1
END
GO

CREATE PROCEDURE Address_RestoreAll
    @AddressId int
AS
BEGIN
    UPDATE Address SET IsDeleted = 0
END
GO

